package database;

import javax.swing.*;
import java.sql.*;
import java.util.*;

public class JdbcRoom {
    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    final String DB_URL = "jdbc:mysql://127.0.0.1:3306/javaroommanage?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";

    Scanner sca = new Scanner(System.in);

    // 数据库的用户名与密码，需要根据自己的设置
    final String USER = "root";
    final String PASS = "root";
    Room room;

    public ArrayList getRName() {
        return RName;
    }

    public void setRName(ArrayList RName) {
        this.RName = RName;
    }

    ArrayList RName;
    String [] select = {"name",//寝室昵称
    "Rnum",//寝室号
    "count",//寝室人数
    "sex",//男寝还是女寝
    "sanitate",//寝室卫生
    "award",//寝室获奖情况
    "studentname"};//寝室长名字

    public JdbcRoom(int service,ArrayList  list1){//判断是哪个服务
        switch (service){
            case 1:JFindRoom(list1);break;//查找寝室
            case 2:JFindStudent(list1);break;//查找寝室长
            case 3:JAccessSanitateList();break;//获取卫生列表
            case 4:JInsertRoom(list1);break;//插入寝室
            case 5:JChangeStudent(list1);break;//修改寝室
            case 6:DeleteRoom(list1);break;//删除寝室
            case 7:JFindAllRoom();break;//所有寝室显示
        }
    }
    public void JFindRoom(ArrayList arrayList){
        int num = 0;
        num = (int)arrayList.get(0);//获取第一个值，就是要找的寝室号码
        System.out.println(num);
        int n = num;
        String sql;
        sql = "SELECT * FROM room WHERE Rnum = "+n;
        ExcuateJdbcAndSql(1,sql,n);
    }
    public void JFindStudent(ArrayList list){
        String sname;
        sname = (String) list.get(0);
        String sql;
        sql = "SELECT * FROM room WHERE studentname = '"+sname+"'";

        ExcuateJdbcAndSql(2,sql,1);
    }
    public void JAccessSanitateList(){
        String sql;
        sql = "SELECT Rnum,sanitate FROM room ";
        ExcuateJdbcAndSql(3,sql,1);
    }
    public void JChangeStudent(ArrayList arrayList){
        String sql;
        int id = (int)arrayList.get(1);
        sql = "UPDATE room SET name = '"+arrayList.get(0)+"',count = "+arrayList.get(2)+
                ",sex = '"+arrayList.get(3)+"',sanitate = '"+arrayList.get(4)+"',award = '"+arrayList.get(5)+"',studentname = '"+arrayList.get(6)+"' WHERE rnum = "+arrayList.get(1);

        ExcuateJdbcAndSql(5,sql,id);
    }
    public void JInsertRoom(ArrayList arrlist){
        int i=0;
        Iterator it = arrlist.iterator();
        TreeMap tm = new TreeMap();
        while(it.hasNext()){
            if(i==1||i==2){
                int a = (int)it.next();
                tm.put(select[i],a);
            }
            else {
                String a = (String) it.next();
                tm.put(select[i],a);
            }

            i++;
        }
        String sql;
        sql = "INSERT INTO room (name,Rnum,count,sex," +//输入有问题
                "sanitate,award,studentname) VALUES ('" +
                tm.get(select[0])+"',"+tm.get(select[1])+","+tm.get(select[2])+",'"+tm.get(select[3])
                +"','"+tm.get(select[4])+"','"+tm.get(select[5])+"','"+tm.get(select[6])+ "')";
        ExcuateJdbcAndSql(4,sql,(int)tm.get(select[1]));

    }
    public void DeleteRoom(ArrayList list){

        int num = 0;
        num = (int) list.get(0);
        int n = num;
        String sql;
        sql = "DELETE FROM room WHERE Rnum = "+n;
        ExcuateJdbcAndSql(6,sql,n);

    }
    public void JFindAllRoom(){
        String sql;
        sql = "SELECT * FROM room";
        ExcuateJdbcAndSql(7,sql,1);
    }
    public void ExcuateJdbcAndSql(int Jservise,String sql,int roomnumber){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);

            // 执行查询
            System.out.println(" 实例化Statement对象...");
            stmt = conn.createStatement();
            String sql_creattable = "CREATE TABLE IF NOT EXISTS room" +
                    "(Rnum INT NOT NULL," +
                    "name VARCHAR(45)," +
                    "count INT," +
                    "sex VARCHAR(45)," +
                    "sanitate VARCHAR(45)," +
                    "award VARCHAR(45)," +
                    "studentname VARCHAR(45)," +
                    "PRIMARY KEY (Rnum))";
            stmt.executeUpdate(sql_creattable);
            switch (Jservise){
                case 1:
                {
                    rs = stmt.executeQuery("SELECT Rnum FROM room WHERE Rnum = '"+roomnumber+"'");
                    int id=0;
                    int flag=0;
                    while(rs.next()){
                        id = rs.getInt("Rnum");
                        flag=1;
                    }
                    if(flag==0){
                        System.out.println("没有这样的寝室，查找失败");
                        break;
                    }
                    rs = stmt.executeQuery(sql);
                    while(rs.next()){
                        String name = rs.getString("name");
                        int Rnum = rs.getInt("Rnum");
                        int count = rs.getInt("count");
                        String sex = rs.getString("sex");
                        String sanitate = rs.getString("sanitate");
                        String award = rs.getString("award");
                        String studentname = rs.getString("studentname");
                       room = new Room(name,Rnum,count,sex,sanitate,award,studentname);

                    }
                    setRoom(room);
                    break;
                }
                case 2:
                {
                    rs = stmt.executeQuery(sql);
                    while(rs.next()){
                        String name = rs.getString("name");
                        int Rnum = rs.getInt("Rnum");
                        int count = rs.getInt("count");
                        String sex = rs.getString("sex");
                        String sanitate = rs.getString("sanitate");
                        String award = rs.getString("award");
                        String studentname = rs.getString("studentname");
                        room = new Room(name,Rnum,count,sex,sanitate,award,studentname);
                        setRoom(room);
                    }
                    break;
                }
                case 3:
                {
                    int ss = 0;
                    rs = stmt.executeQuery(sql);
                    RName = new ArrayList();
                    while(rs.next()){
                        RName.add( rs.getString("sanitate"));
                    }
                    Collections.sort(RName);
                    break;
                }
                case 4:
                {
                    int id=0;
                    rs = stmt.executeQuery("SELECT * FROM room WHERE Rnum = "+roomnumber);
                    while(rs.next()) {
                        id = rs.getInt("Rnum");
                    }
                    int fk = 1;
                    if(id>0){
                        System.out.println("已存在该寝室，无法添加");
                        fk=0;
                    }
                    if(fk==1)
                        stmt.executeUpdate(sql);
                    break;
                }
                case 5:
                {
                    int id=0;
                    rs = stmt.executeQuery("SELECT * FROM room WHERE Rnum = "+roomnumber);
                    while(rs.next()) {
                        id = rs.getInt("Rnum");
                    }
                    if(id>0) {
                        stmt.executeUpdate(sql);
                    }
                    else {
                        JFrame jf= new JFrame("修改失败界面");
                        jf.setBounds(100,200,200,250);
                        JLabel fault1 = new JLabel("没有这样的用户",SwingConstants.CENTER);
                        jf.add(fault1);
                        jf.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
                        jf.setVisible(true);
                    }
                    break;
                }
                case 6:
                {
                    rs = stmt.executeQuery("SELECT Rnum FROM room WHERE Rnum = '"+roomnumber+"'");
                    int id=0;
                    while(rs.next()){
                        id = rs.getInt("Rnum");

                    }
                    if(id>0){
                        stmt.executeUpdate(sql);
                    }
                    else System.out.println("没有这样的寝室，不可删除");

                    break;
                }
                case 7:
                {
                    rs = stmt.executeQuery(sql);
                    RName = new ArrayList();
                    while(rs.next()){
                        String name = rs.getString("name");
                        int Rnum = rs.getInt("Rnum");
                        int count = rs.getInt("count");
                        String sex = rs.getString("sex");
                        String sanitate = rs.getString("sanitate");
                        String award = rs.getString("award");
                        String studentname = rs.getString("studentname");
                        room = new Room(name,Rnum,count,sex,sanitate,award,studentname);
                        RName.add(room);
                    }
                    break;
                }
            }
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            if(rs != null){
                try{
                    rs.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
                rs = null;
            }
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }// 什么都不做
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        System.out.println("Goodbye!");
    }
    public void setRoom(Room room){
        this.room = room;
    }
    public Room getRoom(){
        return room;
    }
}
